package DB;

import Model.Teacher;

import java.sql.*;
import java.util.ArrayList;

public class TeacherDB implements TeacherDBIF {
    private Connection conn;
    private ConnectionDB connectionDB;

    public TeacherDB() {
        conn = ConnectionDB.getInstance().getConn();
        connectionDB = ConnectionDB.getInstance();
    }

    @Override
    public int insertTeacher(Teacher teacher) {
        int rc = -1;
        PreparedStatement pstmtTeacher;
        PreparedStatement pstmtPerson;

        String insertTeacher = "INSERT INTO Teacher (teacherId, initials)" + "VALUES (?,?)";
        String insertPerson = "INSERT INTO Person (id, name, username, password, address, email, phoneNo, type)" + "VALUES (?,?,?,?,?,?,?,?)";

        try {

            connectionDB.startTransaction();

            pstmtTeacher = conn.prepareStatement(insertTeacher);
            pstmtTeacher.setInt(1, teacher.getTeacherId());
            pstmtTeacher.setString(2, teacher.getInitials());
            pstmtTeacher.executeUpdate();

            pstmtPerson = conn.prepareStatement(insertPerson);
            pstmtPerson.setInt(1, teacher.getId());
            pstmtPerson.setString(2, teacher.getName());
            pstmtPerson.setString(3, teacher.getUsername());
            pstmtPerson.setString(4, teacher.getPassword());
            pstmtPerson.setString(5, teacher.getAddress());
            pstmtPerson.setString(6, teacher.getEmail());
            pstmtPerson.setInt(7, teacher.getPhoneNo());
            pstmtPerson.setString(8, teacher.getType());
            rc = pstmtPerson.executeUpdate();

            connectionDB.commitTransaction();
        }
        catch (Exception e) {
            System.out.println(e);
            connectionDB.rollbackTransaction();
        }

        return rc;
    }

    @Override
    public Teacher findTeacherByID(int id) {
        return singleWhere("id = " + id + " AND teacherId = " + id , false);
    }

    @Override
    public Teacher findTeacher(String userName, String password) {
        return singleWhere("id = teacherId AND username = '" + userName + "' AND password = '" + password + "'", false);
    }

    @Override
    public ArrayList<Teacher> getAllTeachers(boolean retrieveAssociation) {
        return miscWhere("id = teacherId ", false);
    }

    @Override
    public int updateTeacher(Teacher teacher) {
        Teacher teacherObj = teacher;
        int rc = -1;

        String queryPerson = "UPDATE Person SET " +
                "name = '" + teacherObj.getName() + "', " +
                "username = '" + teacherObj.getUsername() + "', " +
                "password = '" + teacherObj.getPassword() + "', " +
                "address = ' " + teacherObj.getAddress() + "', " +
                "email = '" + teacher.getEmail() + "', " +
                "phoneNo = '" + teacherObj.getPhoneNo() + "', " +
                "type = '" + teacherObj.getType() + "' " +
                " WHERE id = " + teacher.getId() + " AND type = 'Teacher'";

        String queryTeacher = "UPDATE Teacher SET " +
                "initials = '" + teacherObj.getInitials() + "'" +
                " WHERE teacherId = " + teacherObj.getTeacherId();

        System.out.println(queryPerson);
        System.out.println(queryTeacher);

        try {
            Statement stmtPerson = conn.createStatement();
            stmtPerson.setQueryTimeout(5);
            stmtPerson.executeUpdate(queryPerson);
            stmtPerson.close();

            Statement stmtTeacher = conn.createStatement();
            stmtTeacher.setQueryTimeout(5);
            rc = stmtTeacher.executeUpdate(queryTeacher);
            stmtTeacher.close();
        }
        catch (Exception e) {
            System.out.println("Update exception in Teacher db :" +e);
        }
        return rc;
    }

    @Override
    public int deleteTeacher(int id) {
        int rc = -1;

        String deleteTeacher = "DELETE FROM Teacher WHERE teacherId = '" + id + "'";
        String deletePerson = "DELETE FROM Person WHERE id = '" + id + "'";

        try {
            connectionDB.startTransaction();
            Statement stmt = conn.createStatement();
            stmt.setQueryTimeout(5);
            stmt.executeUpdate(deleteTeacher);
            rc = stmt.executeUpdate(deletePerson);
            stmt.close();
            connectionDB.commitTransaction();
        }
        catch (Exception e) {
            System.out.println(e);
            connectionDB.rollbackTransaction();
        }

        return rc;
    }

    //Finding a single Teacher
    private Teacher singleWhere(String wClause, boolean retrieveAssociation) {
        ResultSet results;
        Teacher teachObj = new Teacher();

        String query =  buildQuery(wClause);
        System.out.println(query);

        try{
            Statement stmt = conn.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);

            if( results.next() ){
                teachObj = buildTeacher(results);
                stmt.close();
            }
            else{
                teachObj = null;
            }
        }
        catch(Exception e){
            System.out.println("Query exception: "+e);
        }
        return teachObj;
    }

    //Finding all Teachers
    private ArrayList<Teacher> miscWhere(String wClause, boolean retrieveAssociation) {
        ResultSet results;
        ArrayList<Teacher> list = new ArrayList<Teacher>();

        String query = buildQuery(wClause);

        try {
            Statement stmt = conn.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);

            while (results.next()) {
                Teacher teacherObj = buildTeacher(results);
                list.add(teacherObj);
            }
            System.out.println(query);

            stmt.close();
        }
        catch (Exception e) {
            System.out.println("Query exception: " + e);
            e.printStackTrace();
        }

        return list;
    }

    private String buildQuery(String wClause)
    {
        String query="SELECT teacherId, initials, id, name, username, password, address, email, phoneNo, type FROM Teacher, Person";

        if (wClause.length()>0)
            query=query+" WHERE "+ wClause;

        return query;
    }

    private Teacher buildTeacher(ResultSet results){

        Teacher teacherObj = new Teacher();

        try{
            teacherObj.setId(results.getInt("id"));
            teacherObj.setName(results.getString("name"));
            teacherObj.setUsername(results.getString("username"));
            teacherObj.setPassword(results.getString("password"));
            teacherObj.setAddress(results.getString("address"));
            teacherObj.setEmail(results.getString("email"));
            teacherObj.setPhoneNo(results.getInt("phoneNo"));
            teacherObj.setType(results.getString("type"));
            teacherObj.setTeacherId(results.getInt("teacherId"));
            teacherObj.setInitials(results.getString("initials"));
        }
        catch (SQLException e){
            System.out.println("Error building Teacher object" + e);
        }

        return teacherObj;
    }

    public int teacherGetMax() {
        int nextId = GetMax.getMaxId("SELECT max(id) from Person");

        nextId = nextId +1;

        return nextId;
    }


}
